package com.logisticsmanagement.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.logisticsmanagement.domain.City;
import com.logisticsmanagement.domain.Indent;
import com.logisticsmanagement.domain.Route;
import com.logisticsmanagement.utils.DataSource;
import com.logisticsmanagement.utils.DatabaseUtil;

public class IndentDao implements Serializable {
	
	/**
	 * query indent through special indentID
	 * @param id
	 * @return
	 */
	public Indent selectById(Integer id){

		Connection conn = null;
		Indent indent =null;
		try {
			conn= DataSource.getConnection();
			if(conn == null)
				return null;
			PreparedStatement sta = conn.prepareStatement("select * from indent where orderID=?");
			sta.setInt(1, id);
			ResultSet rs = sta.executeQuery();
			
			if(rs.next()){
				indent =  new Indent();			
				
				indent.setOrderID(rs.getInt("orderID"));
				indent.setTruckID(rs.getInt("truckID"));
				
				indent.setSenderID(rs.getString("senderID"));
				indent.setReceiverDeliveryStationID(rs.getInt("receiverDeliveryStationID"));
				indent.setCurrentCityID(rs.getInt("currentCityID"));
				indent.setState(rs.getInt("state"));
				indent.setCreateTimeStamp(rs.getDate("createTimeStamp"));//createTimeStamp
				
				//建议：设置一个默认的到达时间，要不然没到的话就设置为一个值
				indent.setFinishTimeStamp(rs.getDate("finishTimeStamp"));//finishTimeStamp
				indent.setSendTimeStamp(rs.getDate("sendTimeStamp"));//sendTimeStamp
				indent.setReceiverName(rs.getString("receiverName"));
				indent.setReceiverAddress(rs.getString("receiverAddress"));
				indent.setReceiverPhoneNumber(rs.getString("receiverPhoneNumber"));
				indent.setGoodCategore(rs.getString("goodCategore"));
				indent.setGoodWeight(rs.getDouble("goodWeight"));
				indent.setGoodName(rs.getString("goodName"));
				indent.setGoodTotalPrice(rs.getDouble("goodTotalPrice"));
				indent.setPayMode(rs.getInt("payMode"));
				indent.setPrepareDelete(rs.getBoolean("prepareDelete"));
				indent.setIsDelete(rs.getBoolean("isDelete"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		return indent;
	}
	
	
	
	
	/**
	 * this method to add a new indent to database
	 * @param newIndent
	 * @return
	 */
	public Boolean add(Indent newIndent){
		
		Connection conn = null;
		try {
			conn= DataSource.getConnection();
			if(conn == null)
				return null;
			PreparedStatement sta = conn.prepareStatement("insert into "
					+ "indent(orderID,truckID,senderID,receiverDeliveryStationID,"
					+ "currentCityID,state,createTimeStamp,finishTimeStamp,sendTimeStamp,"
					+ "receiverName,receiverAddress,receiverPhoneNumber,goodCategore,"
					+ "goodWeight,goodName,goodTotalPrice,payMode,prepareDelete,isDelete)"
					+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			
			sta.setInt(1,newIndent.getOrderID());
			sta.setInt(2,newIndent.getTruckID());
			sta.setString(3, newIndent.getSenderID());
			sta.setInt(4,newIndent.getReceiverDeliveryStationID());
			sta.setInt(5,newIndent.getCurrentCityID());
			sta.setInt(6,newIndent.getState());
			sta.setTimestamp(7, new Timestamp(new Date().getTime()));//createTimeStamp
			sta.setTimestamp(8, new Timestamp(new Date().getTime()));//finishTimeStamp
			sta.setTimestamp(9, new Timestamp(new Date().getTime()));//sendTimeStamp
			sta.setString(10, newIndent.getReceiverName());
			sta.setString(11, newIndent.getReceiverAddress());
			sta.setString(12, newIndent.getReceiverPhoneNumber());
			sta.setString(13, newIndent.getGoodCategore());
			sta.setDouble(14, newIndent.getGoodWeight());
			sta.setString(15, newIndent.getGoodName());
			sta.setDouble(16, newIndent.getGoodTotalPrice());
			sta.setInt(17,newIndent.getPayMode());
			sta.setBoolean(18, (newIndent.getPrepareDelete()==true)?true:false);
			sta.setBoolean(19, (newIndent.getIsDelete()==true)?true:false);
			
			return (sta.executeUpdate()==1);
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		return false;
	}

	public boolean update(Indent indent){
		
		Connection conn = null;
//		Indent indent =null;
		try {
			conn= DataSource.getConnection();
			if(conn == null)
				return false;
			PreparedStatement sta = conn.prepareStatement("update indent set truckID=?,senderID=?,"
					+ "receiverDeliveryStationID=?,currentCityID=?,state=?,createTimeStamp=?,"
					+ "finishTimeStamp=?,sendTimeStamp=?,receiverName=?,receiverAddress=?,"
					+ "receiverPhoneNumber=?,goodCategore=?,goodWeight=?,goodName=?,"
					+ "goodTotalPrice=?,payMode=?,prepareDelete=?,isDelete=? where orderID="+indent.getOrderID());
			
			sta.setInt(1,indent.getTruckID());
			sta.setString(2,indent.getSenderID());
			sta.setInt(3, indent.getReceiverDeliveryStationID());
			sta.setInt(4,indent.getCurrentCityID());
			sta.setInt(5,indent.getState());
			sta.setDate(6,(java.sql.Date) indent.getCreateTimeStamp());
			sta.setDate(7, (java.sql.Date) indent.getFinishTimeStamp());
			sta.setDate(8, (java.sql.Date) indent.getSendTimeStamp());
			sta.setString(9, indent.getReceiverName());
			sta.setString(10, indent.getReceiverAddress());
			sta.setString(11, indent.getReceiverPhoneNumber());
			sta.setString(13, indent.getGoodCategore());
			sta.setDouble(14, indent.getGoodWeight());
			sta.setString(15, indent.getGoodName());
			sta.setDouble(16, indent.getGoodTotalPrice());
			sta.setInt(17,indent.getPayMode());
			sta.setBoolean(18, (indent.getPrepareDelete()==true)?true:false);
			sta.setBoolean(19, (indent.getIsDelete()==true)?true:false);
			
			sta.executeUpdate();
			return true;
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * count amount of indent with certain attribute
	 * @param indent
	 * @return
	 */
	public Integer selectCount(Indent indent){
		
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			sta = DatabaseUtil.getSelcectCountStatement(indent, conn);	//define sql query statement, then get operation statement object of database
			rs = sta.executeQuery();		//query database, return ResultSet
			if(rs.next()){
				return rs.getInt(1);
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try { 	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return null;
	}
	
	/**
	 * the criteria query,the paging query 
	 * @param indent
	 * @param nowPage
	 * @param pageSize
	 * @return
	 */
 	public List<Indent> selectPage(Indent indent,int nowPage,int pageSize, String orderBy){
		
 		List<Indent> ret = new ArrayList<Indent>();
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		if(orderBy == null)
			orderBy = "orderID";
		
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			//define sql query statement, then get operation statement object of database
			sta = DatabaseUtil.getSelectPageStatment(indent, conn, nowPage, pageSize, orderBy);	 
			rs = sta.executeQuery();		//query database, return ResultSet
			//traverse packaged object
			while(rs.next()){
				Indent retIndent =  new Indent();
				retIndent.setOrderID(rs.getInt(1));
				retIndent.setTruckID(rs.getInt(2));
				retIndent.setSenderID(rs.getString(3));
				retIndent.setReceiverDeliveryStationID(rs.getInt(4));
				retIndent.setCurrentCityID(rs.getInt(5));
				retIndent.setState(rs.getInt(6));
				retIndent.setCreateTimeStamp(rs.getDate(7));//createTimeStamp
				retIndent.setFinishTimeStamp(rs.getDate(8));//finishTimeStamp
				retIndent.setSendTimeStamp(rs.getDate(9));//sendTimeStamp
				retIndent.setReceiverName(rs.getString(10));
				retIndent.setReceiverAddress(rs.getString(11));
				retIndent.setReceiverPhoneNumber(rs.getString(12));
				retIndent.setGoodCategore(rs.getString(13));
				retIndent.setGoodWeight(rs.getDouble(14));
				retIndent.setGoodName(rs.getString(15));
				retIndent.setGoodTotalPrice(rs.getDouble(16));
				retIndent.setPayMode(rs.getInt(17));
				retIndent.setPrepareDelete(rs.getBoolean(18));
				retIndent.setIsDelete(rs.getBoolean(19));
				ret.add(retIndent);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return ret;
	}
	
}
